import pymysql

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'lmj1764432683',
    'database': 'night_snack_ordering_system',
    'port': 3306
}

try:
    # 连接到MySQL服务器
    print("正在连接到MySQL服务器...")
    conn = pymysql.connect(
        host=DB_CONFIG['host'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        port=DB_CONFIG['port']
    )
    cursor = conn.cursor()
    print("成功连接到MySQL服务器！")
    
    # 创建数据库
    db_name = DB_CONFIG['database']
    print(f"正在创建数据库 {db_name}...")
    cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    print(f"数据库 {db_name} 创建成功！")
    
    # 选择数据库
    conn.select_db(db_name)
    print(f"已选择数据库 {db_name}")
    
    # 创建用户表
    print("正在创建用户表...")
    create_user_table = """
    CREATE TABLE IF NOT EXISTS user (
        user_id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        role ENUM('student', 'delivery_person') NOT NULL,
        name VARCHAR(50) NOT NULL,
        phone VARCHAR(20) NOT NULL UNIQUE,
        address VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    cursor.execute(create_user_table)
    print("用户表创建成功！")
    
    # 插入测试用户
    print("正在插入测试用户数据...")
    insert_users = """
    INSERT INTO user (username, password, role, name, phone, address) VALUES
    ('student1', 'password123', 'student', '张三', '13800138001', '学生公寓1号楼302室'),
    ('delivery1', 'password456', 'delivery_person', '李四', '13800138002', '配送中心')
    """
    cursor.execute(insert_users)
    print("测试用户数据插入成功！")
    
    # 创建其他必要的表（菜品表、订单表、订单详情表）
    print("正在创建菜品表...")
    create_food_table = """
    CREATE TABLE IF NOT EXISTS food (
        food_id INT PRIMARY KEY AUTO_INCREMENT,
        food_name VARCHAR(100) NOT NULL UNIQUE,
        description TEXT,
        price DECIMAL(10,2) NOT NULL,
        image_path VARCHAR(255),
        category VARCHAR(50) NOT NULL,
        stock INT NOT NULL DEFAULT 100,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    cursor.execute(create_food_table)
    print("菜品表创建成功！")
    
    print("正在创建订单表...")
    create_order_table = """
    CREATE TABLE IF NOT EXISTS `order` (
        order_id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        order_status ENUM('待支付', '已支付', '配送中', '已完成') NOT NULL DEFAULT '待支付',
        order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        total_amount DECIMAL(10,2) NOT NULL,
        delivery_person_id INT,
        delivery_time TIMESTAMP,
        completion_time TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE,
        FOREIGN KEY (delivery_person_id) REFERENCES user(user_id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    cursor.execute(create_order_table)
    print("订单表创建成功！")
    
    print("正在创建订单详情表...")
    create_order_detail_table = """
    CREATE TABLE IF NOT EXISTS order_detail (
        order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
        order_id INT NOT NULL,
        food_id INT NOT NULL,
        quantity INT NOT NULL,
        unit_price DECIMAL(10,2) NOT NULL,
        subtotal DECIMAL(10,2) NOT NULL,
        FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,
        FOREIGN KEY (food_id) REFERENCES food(food_id) ON DELETE CASCADE,
        UNIQUE KEY unique_order_food (order_id, food_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    cursor.execute(create_order_detail_table)
    print("订单详情表创建成功！")
    
    # 插入测试菜品
    print("正在插入测试菜品数据...")
    insert_foods = """
    INSERT INTO food (food_name, description, price, image_path, category, stock) VALUES
    ('香辣小龙虾', '新鲜小龙虾，香辣可口', 88.00, 'images/default_food.jpg', '海鲜', 50),
    ('烤羊肉串', '新疆风味羊肉串，外焦里嫩', 5.00, 'images/default_food.jpg', '烧烤', 100),
    ('炒面', '特色炒面，配料丰富', 15.00, 'images/default_food.jpg', '主食', 80),
    ('冰啤酒', '清爽冰啤酒，消暑解渴', 10.00, 'images/default_food.jpg', '饮品', 200),
    ('水果沙拉', '新鲜水果，健康营养', 20.00, 'images/default_food.jpg', '沙拉', 60)
    """
    cursor.execute(insert_foods)
    print("测试菜品数据插入成功！")
    
    # 提交事务
    conn.commit()
    print("数据库初始化成功！所有表已创建并插入测试数据。")
    
    # 验证数据
    print("\n验证用户数据:")
    cursor.execute("SELECT username, password, role FROM user")
    users = cursor.fetchall()
    for user in users:
        print(f"用户名: {user[0]}, 密码: {user[1]}, 角色: {user[2]}")
    
    print("\n验证菜品数据:")
    cursor.execute("SELECT food_name, price, category FROM food LIMIT 3")
    foods = cursor.fetchall()
    for food in foods:
        print(f"菜品名称: {food[0]}, 价格: {food[1]}, 分类: {food[2]}")
    
except Exception as e:
    print(f"数据库初始化失败: {e}")
    import traceback
    traceback.print_exc()
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()
    
print("\n数据库初始化脚本执行完毕。")